package com.tarena.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import com.tarena.entity.Manager;
import com.tarena.entity.Teacher;
import com.tarena.entity.User;
import com.tarena.util.DBUtil;

public class UserDAOJdbcImpl implements UserDAO{
	private static final String FIND_USER = "select * from test_user where name=?";
	private static final String INSERTUSER = "insert into test_user" +
			"(name,password,phone) values(?,?,?)";
	private static final String FIND_ALL = "select * from test_teacher";
	private static final String FIND_TEACHER = "select * from test_teacher where id=?";
	private static final String FIND_TEACHER_NAME = "select * from test_teacher where name=?";
	private static final String UPDATE_TEACHER = 
		"update test_teacher set userqty=?,totalScore=?,averageScore=? where name=?";
	private static final String INSERT_TEACHER = "insert into test_teacher" +
			"(name,age,gender,userqty,totalScore,averageScore) values(?,?,?,?,?,?)";
	private static final String DELETETEACHER = "delete from test_teacher where id=?";
	private static final String FINDMANAGER = "select * from test_manager where name=?";
	private static final String IS_SCORED = "select * from test_user_teacher where user_id =? and teacher_id=?";
	private static final String INSERT_SCORED = "insert into test_user_teacher(user_id,teacher_id,comment) values (?,?,?);";
	private static final String INIT = "truncate table test_user_teacher";
	private static final String UPDATE_UT = "update test_teacher set userqty=?,totalScore=?,averageScore=?";
	private static final String FIND_COMMENTS = "select * from test_user_teacher where teacher_id=? limit ?,?";
	private static final String TOTAL_PAGE = "select count(*) from test_user_teacher where teacher_id=?";
	
	public User findUser(User user1) throws Exception {
		User user = null;
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = conn.prepareStatement(FIND_USER);
		stmt.setString(1, user1.getName());
		ResultSet rs = stmt.executeQuery();
		if(rs.next()){
			user = new User();
			user.setId(rs.getInt("id"));
			user.setName(rs.getString("name"));
			user.setPassword(rs.getString("password"));
			user.setPhone(rs.getString("phone"));
		}
		DBUtil.close(conn);
		return user;
	}

	public void insertUser(User user) throws Exception {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = conn.prepareStatement(INSERTUSER);
		stmt.setString(1, user.getName());
		stmt.setString(2, user.getPassword());
		stmt.setString(3, user.getPhone());
		stmt.executeUpdate();
		DBUtil.close(conn);
		
	}

	public List<Teacher> findAll() throws Exception {
		List<Teacher> teachers = new ArrayList<Teacher>();
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = conn.prepareStatement(FIND_ALL);
		ResultSet rs = stmt.executeQuery();
		while(rs.next()){
			Teacher teacher = new Teacher();
			teacher.setId(rs.getInt("id"));
			teacher.setName(rs.getString("name"));
			teacher.setAge(rs.getInt("age"));
			teacher.setGender(rs.getString("gender"));
			teacher.setUserqty(rs.getInt("userqty"));
			teacher.setTotalScore(rs.getDouble("totalScore"));
			teacher.setAverageScore(rs.getDouble("averageScore"));
			teachers.add(teacher);
		}
		DBUtil.close(conn);
		return teachers;
	}

	public void updateTeacher(Teacher teacher) throws Exception {
		UserDAO dao = new UserDAOJdbcImpl();
		Teacher t = dao.findTeacherName(teacher);
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = conn.prepareStatement(UPDATE_TEACHER);
		int qty = t.getUserqty() + 1;
		stmt.setInt(1, qty);
		double total = teacher.getAssess().getArranged() + teacher.getAssess().getExplain() +
		teacher.getAssess().getExpress() + teacher.getAssess().getInteractive() + 
		teacher.getAssess().getQuestion() + t.getTotalScore();
		stmt.setDouble(2, total);
		DecimalFormat df = new DecimalFormat(".#");
		double average = Double.parseDouble(df.format(total/qty));
		stmt.setDouble(3, average);
		stmt.setString(4, teacher.getName());
		stmt.executeUpdate();
		DBUtil.close(conn);
	}

	public Teacher findTeacher(Teacher teacher1) throws Exception {
		Connection conn = DBUtil.getConnection();
		Teacher teacher = null;
		PreparedStatement stmt = conn.prepareStatement(FIND_TEACHER);
		stmt.setInt(1, teacher1.getId());
		ResultSet rs = stmt.executeQuery();
		if(rs.next()){
			teacher = new Teacher();
			teacher.setId(rs.getInt("id"));
			teacher.setName(rs.getString("name"));
			teacher.setAge(rs.getInt("age"));
			teacher.setGender(rs.getString("gender"));
			teacher.setUserqty(rs.getInt("userqty"));
			teacher.setTotalScore(rs.getDouble("totalScore"));
			teacher.setAverageScore(rs.getDouble("averageScore"));
		}
		DBUtil.close(conn);
		return teacher;
	}

	public void insertTeacher(Teacher teacher) throws Exception {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = conn.prepareStatement(INSERT_TEACHER);
		stmt.setString(1, teacher.getName());
		stmt.setInt(2, teacher.getAge());
		stmt.setString(3, teacher.getGender());
		stmt.setInt(4, 0);
		stmt.setDouble(5, 0);
		stmt.setDouble(6, 0);
		stmt.executeUpdate();
		DBUtil.close(conn);
	}

	public Manager findManager(Manager manager1) throws Exception {
		Connection conn = DBUtil.getConnection();
		Manager manager = null;
		PreparedStatement stmt = conn.prepareStatement(FINDMANAGER);
		stmt.setString(1, manager1.getName());
		ResultSet rs = stmt.executeQuery();
		if(rs.next()){
			manager = new Manager();
			manager.setId(rs.getInt("id"));
			manager.setName(rs.getString("name"));
			manager.setPassword(rs.getString("password"));
		}
		DBUtil.close(conn);
		return manager;
	}

	public void deleteTeacher(Teacher teacher) throws Exception {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = conn.prepareStatement(DELETETEACHER);
		stmt.setInt(1, teacher.getId());
		stmt.executeUpdate();
		DBUtil.close(conn);	
	}

	public boolean isScored(int uid, int tid) throws Exception {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = conn.prepareStatement(IS_SCORED);
		stmt.setInt(1, uid);
		stmt.setInt(2, tid);
		ResultSet rs = stmt.executeQuery();
		if(rs.next()){
			return true;
		}
		return false;
	}

	public Teacher findTeacherName(Teacher teacher1) throws Exception {
		Connection conn = DBUtil.getConnection();
		Teacher teacher = null;
		PreparedStatement stmt = conn.prepareStatement(FIND_TEACHER_NAME);
		stmt.setString(1, teacher1.getName());
		ResultSet rs = stmt.executeQuery();
		if(rs.next()){
			teacher = new Teacher();
			teacher.setId(rs.getInt("id"));
			teacher.setName(rs.getString("name"));
			teacher.setAge(rs.getInt("age"));
			teacher.setGender(rs.getString("gender"));
			teacher.setUserqty(rs.getInt("userqty"));
			teacher.setTotalScore(rs.getDouble("totalScore"));
			teacher.setAverageScore(rs.getDouble("averageScore"));
		}
		DBUtil.close(conn);
		return teacher;
	}

	public void insertScored(int uid, int tid,String comment) throws Exception {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = conn.prepareStatement(INSERT_SCORED);
		stmt.setInt(1, uid);
		stmt.setInt(2, tid);
		stmt.setString(3, comment);
		stmt.executeUpdate();
		DBUtil.close(conn);
	}

	public void init() throws Exception {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = conn.prepareStatement(INIT);
		stmt.executeUpdate();
		DBUtil.close(conn);
	}

	public void updateUT() throws Exception {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = conn.prepareStatement(UPDATE_UT);
		stmt.setInt(1, 0);
		stmt.setInt(2, 0);
		stmt.setInt(3, 0);
		stmt.executeUpdate();
		DBUtil.close(conn);
	}

	public List<String> findComments(int tid,int page,int size) throws Exception {
		List<String> comments = new ArrayList<String>();
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = conn.prepareStatement(FIND_COMMENTS);
		stmt.setInt(1, tid);
		int begin = (page - 1)*size;
		stmt.setInt(2, begin);
		stmt.setInt(3, size);
		ResultSet rs = stmt.executeQuery();
		while(rs.next()){
			comments.add(rs.getString("comment"));
		}
		return comments;
	}

	public int countTotalPage(int tid, int size) throws Exception {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = conn.prepareStatement(TOTAL_PAGE);
		stmt.setInt(1, tid);
		ResultSet rs = stmt.executeQuery();
		rs.next();
		int n = rs.getInt(1);
		if(n == 0){
			return 1;
		}else if(n % size == 0){
			return n / size;
		}else{
			return n / size + 1;
		}
	}

}
